//
//  so_sqlite.swift
//  soapp
//
//  Created by zhu xietong on 2017/9/13.
//  Copyright © 2017年 CocoaPods. All rights reserved.

import UIKit
//import sqlite3// 该pod 安装方式不用提供swift 的桥接文件
import SQLite3

private func errorFromSQLiteResultCode(_ database:OpaquePointer) -> NSError {
    var userInfo: [String:Any]?
    
    let resultCode = sqlite3_errcode(database)
    let errorMsg = sqlite3_errmsg(database)
    if errorMsg != nil {
        if let errorString = NSString(utf8String: errorMsg!) {
            userInfo = [ NSLocalizedDescriptionKey:errorString ]
        }
    }
    
    return NSError(domain:  "sqlite3",
                   code:    Int(resultCode),
                   userInfo:userInfo)
}



public struct DB {
    
    public static var db_name = "defaultDB.db"
    public static var `default` = DB.sqlite.default
    public class sqlite: NSObject {
        static var `default` = DB.sqlite(db_name: DB.db_name)
        
        public var db: OpaquePointer? = nil
        
        public var db_name = DB.db_name
        public init(db_name:String,bundle:Bundle?=nil) {
            super.init()
            self.db_name = db_name
            self.copyDB(bundle:bundle)
        }
        
        @discardableResult
        public func open(database: String) -> Bool {
            let path = database
            print(path)
            if sqlite3_open(path, &db) == SQLITE_OK {
                debugPrint("打开数据库成功")
            } else {
                debugPrint("打开数据库失败")
            }
            return false
        }
        
        
        public func copyDB(bundle:Bundle?=nil) {
            // Set up for file operations
            let fm = FileManager.default
            //		let dbName = String(cString:DB_NAME)
            // Get path to DB in Documents directory
            
            var docDir = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true)[0]
            #if os(OSX)
                let info = Bundle.main.infoDictionary!
                let appName = info["CFBundleName"] as! String
                docDir = (docDir as NSString).appendingPathComponent(appName)
                // Create folder if it does not exist
                if !fm.fileExists(atPath:docDir) {
                    do {
                        try fm.createDirectory(atPath:docDir, withIntermediateDirectories:true, attributes:nil)
                    } catch {
                        NSLog("error creating DB directory: \(docDir) on macOS")
                    }
                }
            #endif
            let path = (docDir as NSString).appendingPathComponent(db_name)
            
            if !(fm.fileExists(atPath:path)) {
                let bd = bundle ?? Bundle.main
//                print("=kkkkJJJ|\(bd.resourcePath)")
                guard let rp = bd.resourcePath else { return }
                let from = (rp as NSString).appendingPathComponent(db_name)
                do {
                    try fm.copyItem(atPath:from, toPath:path)
                } catch let error as NSError {
                    NSLog("so_sqlite - failed to copy writable version of DB!")
                    NSLog("error - \(error.localizedDescription)")
                    return
                }
            }
            
            open(database: path)
        }
        
        
        @discardableResult
        public func exec(sql: String) -> Bool {
            // 1.将sql语句转成c语言字符串
            //        print(sql)
            let cSQL = sql.cString(using: String.Encoding.utf8)
            let errmsg : UnsafeMutablePointer<UnsafeMutablePointer<Int8>?>? = nil
            
            
            if sqlite3_exec(db, cSQL, nil, nil, errmsg) == SQLITE_OK {
                return true
            }else{
                //            debugPrint("exec 语句执行出错: \(sqliteError.localizedDescription);检查语句：（\(sql)）")
                return false
            }
        }
        
        @discardableResult
        public func query(sql : String) -> [[String : Any]] {
            //定义游标对象
            var stmt : OpaquePointer? = nil
            //将需要查询的SQL语句转化为C语言
            if sql.lengthOfBytes(using: String.Encoding.utf8) > 0 {
                let cQuerySQL = (sql.cString(using: String.Encoding.utf8))!
                
                //进行查询前准备操作
                if sqlite3_prepare_v2(db, cQuerySQL, -1, &stmt, nil) == SQLITE_OK {
                    //准备好之后进行解析
                    var queryDataArrM = [[String : Any]]()
                    
                    
                    
                    while sqlite3_step(stmt) == SQLITE_ROW {
                        //1.获取 解析到的列(字段个数)
                        let columnCount = sqlite3_column_count(stmt)
                        //2.遍历某行数据
                        var dict = [String : Any]()
                        for i in 0..<columnCount {
                            // 取出i位置列的字段名,作为字典的键key
                            let cKey = sqlite3_column_name(stmt, i)
                            let key : String = String(validatingUTF8: cKey!)!
                            
                            //取出i位置存储的值,作为字典的值value
                            if let cValue = sqlite3_column_text(stmt, i)
                            {
                                let value =  String(cString:cValue)
                                dict[key] = value as AnyObject
                            }
                        }
                        queryDataArrM.append(dict)
                    }
                    sqlite3_finalize(stmt)
                    return queryDataArrM
                }
                
                
            }
            return [[String : Any]]()
        }
        
        
        @discardableResult
        public func count(sql : String) -> Int {
            var stmt : OpaquePointer? = nil
            if sql.lengthOfBytes(using: String.Encoding.utf8) > 0 {
                let cQuerySQL = (sql.cString(using: String.Encoding.utf8))!
                if sqlite3_prepare_v2(db, cQuerySQL, -1, &stmt, nil) == SQLITE_OK {
                    while( sqlite3_step(stmt) == SQLITE_ROW )
                    {
                        let count = sqlite3_column_int(stmt, 0);
                        let num = Int(count)
                        return num
                    }
                    sqlite3_finalize(stmt)
                }
                else
                {
                    debugPrint("error count database \(sqliteError.localizedDescription)")
                }
                
                
            }
            return 0
        }
        
        public var sqliteError:NSError {
            return errorFromSQLiteResultCode(db!)
        }
        
        
        deinit {
            
            let result = sqlite3_close(db)
            if result != SQLITE_OK {
                NSLog("error closing database (resultCode: \(result)): \(sqliteError.localizedDescription)")
            }
            
        }
        
        
                
        
        
        @discardableResult
        public func insert(_ table:String,_ values:[String:Any]) -> Bool {
            // 1.将sql语句转成c语言字符串
            //        print(sql)
            
            let keys = values.keys.joined(separator: ",")
            var vs = [String]()
            for one in values.keys
            {
                vs.append("\'\(values[one]!)\'")
            }
            let str_values = vs.joined(separator: ",")
            let new_sql = "insert into \(table)(\(keys)) values(\(str_values))"
            //        print(values)
            //        debugPrint("++___D__D_D__+++||\(new_sql)")
            
            let cSQL = new_sql.cString(using: String.Encoding.utf8)
            let errmsg : UnsafeMutablePointer<UnsafeMutablePointer<Int8>?>? = nil
            
            
            if sqlite3_exec(db, cSQL, nil, nil, errmsg) == SQLITE_OK {
                return true
            }else{
                debugPrint("insert failed: \(sqliteError.localizedDescription)")
                return false
            }
        }

    }


    
}

